package com.ds.dataimport.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Excel解析類（支持03、07）
 * @author wangxiaoyu
 * @since 2014-6-26
 * @version 1.0
 *
 */
public class ExcelPaser {
	
	private  static ExcelPaser instance = null;
	
	private ExcelPaser()
	{
	}
	
	public static ExcelPaser getInstance()
	{
		if(null == instance)
		{
			instance = new ExcelPaser();
		}
		return instance;
	}
	/**
	 * 根据文件的路径创建Workbook对象
	 * @param filePath
	 */
	private Workbook getExcelWorkBook(String filePath) {
		InputStream ins = null;
		Workbook book = null;
		try {
			ins=new FileInputStream(new File(filePath));
			book = WorkbookFactory.create(ins);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (ins != null) {
				try {
					ins.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return book;
	}
	
	/**
	 * 以Map的格式存储数
	 * 读取Excel文件的数
	 * @param filePath   excel 文件的
	 * @param headTitle   
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String,List<List<String>>> readEXCELData(String filePath){
		//获取workbook对象
		Workbook workbook=getExcelWorkBook(filePath);
		
		if(null == workbook)
		{
			return Collections.EMPTY_MAP;
		}
		
		//获取sheet页数
		int sheetNum=workbook.getNumberOfSheets();
		//存储excel相关的数据
		Map<String,List<List<String>>> excelContent = new HashMap<String, List<List<String>>>();
		//遍历相关sheet页面获取相关的数据
		if(sheetNum>0){
			for (int index = 0; index < sheetNum; index++) {
				//创建sheet
				Sheet sheet = workbook.getSheetAt(index);
				//获取sheet的名称
				String sheetName= workbook.getSheetName(index);
				//获取相关的数据
				excelContent.put(sheetName, getOneSheetContent(sheet));
			}
		}
		return excelContent;
	}  
	
	/**
	 * 获取单个sheet页内容
	 * @param sheet
	 * @return
	 */
    @SuppressWarnings("unchecked")
	private List<List<String>> getOneSheetContent(Sheet sheet) {
    	if(null == sheet)
    	{
    		return Collections.EMPTY_LIST;
    	}
    	//获取起始和结束行
    	int startRow=sheet.getFirstRowNum();
    	int lastRow=sheet.getLastRowNum();
    	int titelCount = getTitleCount(sheet);
    	List<List<String>> oneSheetContent = new ArrayList<List<String>>();
    	for(int rowIndex = startRow;rowIndex <= lastRow;rowIndex++)
    	{
    		Row row= sheet.getRow(rowIndex);
			if(row==null){
				continue;
			}
			List<String> oneRowContent = new ArrayList<String>();
			int firstCellNum=row.getFirstCellNum();
			for (int colIndex = firstCellNum; colIndex <titelCount; colIndex++) {
				Cell cell=row.getCell(colIndex);
				//获取单个单元格的数据
				Object cellValue = getCellValue(cell);
				oneRowContent.add(cellValue.toString());
			}
			oneSheetContent.add(oneRowContent);
    	}
		return oneSheetContent;
	}

private int getTitleCount(Sheet sheet) {
	Row fristRow = sheet.getRow(0);
	if(null == fristRow)
	{
		return 0;
	}
	return fristRow.getLastCellNum();
	}

/**
 * 獲取單元格內容
 * @param cell
 * @return
 */
private Object getCellValue(Cell cell) {
	
	if(null == cell)
	{
		return "";
	}
	Object value = "";
	switch(cell.getCellType())
	{
	case Cell.CELL_TYPE_STRING:
		value = cell.getRichStringCellValue();
	case Cell.CELL_TYPE_NUMERIC:
		value = cell.getRichStringCellValue();
	case Cell.CELL_TYPE_BOOLEAN:
		value = cell.getRichStringCellValue();
	default:
	}
	return value;
	}

public static void main(String[] args) {
	Map<String,List<List<String>>> excelContent = ExcelPaser.getInstance().readEXCELData("F:\\数字资源整合\\marcexcel.xls");
	System.out.println(excelContent.size());
	System.out.println("記錄條數：" + excelContent.get("书目").size());
	for(List<String> oneRow : excelContent.get("书目"))
	{
		for(String cellValue : oneRow)
		{
			System.out.print(cellValue);
			System.out.print("@");
		}
		System.out.println(oneRow.size());
	}
}
}
